package edu.zut.dao.impl;

import edu.zut.dao.EmpDao;
import edu.zut.pojo.Department;
import edu.zut.pojo.Employee;
import edu.zut.pojo.Page;
import edu.zut.util.DBUtils;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class EmpDaoImpl implements EmpDao {
    /**
     * 查询全部员工信息
     * @return
     * DBUtils.jar
     */
    @Override
    public List<Employee> queryEmp(String emp_name) {
        String sql = "select e.*,d.dep_name from employee e,department d where e.dep_id = d.id";
        ResultSet rs = null;
        if (null != emp_name){
            sql += " and name like ?";
            rs = DBUtils.query(sql, '%' + emp_name + '%');
        }else {
            rs = DBUtils.query(sql,null);
        }
        List<Employee> emps = new ArrayList<>();
        try {
            while (rs.next()){
                Employee employee = new Employee();
                employee.setId(rs.getInt("id"));
                employee.setAddress(rs.getString("address"));
                employee.setAge(rs.getInt("age"));
                employee.setEdu(rs.getString("edu"));
                employee.setDuty(rs.getString("duty"));
                employee.setDepId(rs.getInt("dep_id"));
                employee.setMajor(rs.getString("major"));
                employee.setName(rs.getString("name"));
                employee.setPhone(rs.getString("phone"));
                employee.setEntryTime(rs.getDate("entry_time"));
                employee.setSchool(rs.getString("school"));
                employee.setSex(rs.getString("sex"));
                employee.setDep_name(rs.getString("dep_name"));
                emps.add(employee);
            }
        }catch (Exception e){
            e.printStackTrace();
        }

        return emps;
    }

    @Override
    public List<Department> queryDep() {
        String sql = "select * from department";
        ResultSet rs = DBUtils.query(sql, null);
        ArrayList<Department> departments = new ArrayList<>();
        try {
            while (rs.next()){
                Department department = new Department();
                department.setId(rs.getInt("id"));
                department.setDep_name(rs.getString("dep_name"));
                departments.add(department);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return departments;
    }

    //查询总记录数
    @Override
    public int findCount() {
        String sql = "select count(*) from employee";
        ResultSet rs = DBUtils.query(sql, null);
        int count = 0;
        try {
           count = rs.getInt(1);
        }catch (Exception e){
            e.printStackTrace();
        }
        return count;
    }
    //分页查询
    @Override
    public List<Employee> queryEmpPage(Page<Employee> page) {
        String sql = "select e.*,d.dep_name from employee e,department d where e.dep_id = d.id limit ?,?";
        ResultSet rs = null;
        rs = DBUtils.query(sql,(page.getCurrentPage() - 1) * page.getSize(),page.getSize());
        List<Employee> emps = new ArrayList<>();
        try {
            while (rs.next()){
                Employee employee = new Employee();
                employee.setId(rs.getInt("id"));
                employee.setAddress(rs.getString("address"));
                employee.setAge(rs.getInt("age"));
                employee.setEdu(rs.getString("edu"));
                employee.setDuty(rs.getString("duty"));
                employee.setDepId(rs.getInt("dep_id"));
                employee.setMajor(rs.getString("major"));
                employee.setName(rs.getString("name"));
                employee.setPhone(rs.getString("phone"));
                employee.setEntryTime(rs.getDate("entry_time"));
                employee.setSchool(rs.getString("school"));
                employee.setSex(rs.getString("sex"));
                employee.setDep_name(rs.getString("dep_name"));
                emps.add(employee);
            }
        }catch (Exception e){
            e.printStackTrace();
        }

        return emps;
    }
}
